package cn.lnexin.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

//////////////////////////////////////////////////////////////////////////////////
//		注意jar包版本,新版(应该是6.0以上)的driverClass格式不一样						//
//		old :com.mysql.jdbc.Driver												//
//		6.0+:com.mysql.cj.jdbc.Driver											//
//		url连接中需要带上serverTimezone=UTC										//
//////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////
//		jdbc_driver=com.mysqljdbc.Driver										//
//		jdbc_url=jdbc:mysql://localhost:3306/log?characterEncoding=utf-8		//
//		jdbc_username=root														//
//		jdbc_password=admin														//
//////////////////////////////////////////////////////////////////////////////////

/**
 * @author lnexin@aliyun.com
 * 
 *         <li>
 *         1. 执行单条: executeStat(String sql) /executePstamt(String sql, List params)----:return boolean
 *         <li>
 *         2. 批量执行: batchExecutePstamt(String sql, List<List> lists)----:return boolean 可以更改为受影响数量
 *         <li>
 *         3. 单条记录查询 : selectOne(String sql, List params)----:return map 一条记录的集合
 *         <li>
 *         4. 多条查询 : selectMore(String sql, List params)----:return List(map);
 *         <li>
 *         5. 列名查询: selectColumns(String tanleN)----:return list(string);
 *         <li>
 *         6. 建表语句 : createTableSql(String tableName, List<String> fields, String engine)----:return string;
 *         <li>
 *         7. 插入语句 : getInsertSql(String tableName, List<String> fields)----:return string;
 *         <li>
 *         8. 验证表是否存在: validateTableExist(String tableName)----:return boolean
 *         <li>
 *         9. 关闭连接: releaseConn()
 */
public class DataBaseUtil {
	// 数据库配置文件地址
	private static final String CONFIG_PATH = "config.properties";
	
	private static final String DB_DRIVER;
	private static final String DB_URL;
	private static final String DB_USERNAME;
	private static final String DB_PASSWORD;
	
	private Connection connection;
	private PreparedStatement pstmt;
	private ResultSet resultSet;
	
	static {
		Properties p = new Properties();
		try {
			// 两种方式任选其一
			// InputStream u = DataBaseUtil.class.getResourceAsStream("config.properties");
			// p.load(u);
			p.load(new FileInputStream(new File(CONFIG_PATH)));
		} catch (FileNotFoundException e) {
			System.err.println("Not Database configuration files !");
			e.printStackTrace();
		} catch (IOException e) {
			System.err.println("Read configuration file failure!");
			e.printStackTrace();
		}
		DB_DRIVER = p.getProperty("jdbc_driver");
		DB_URL = p.getProperty("jdbc_url");
		DB_USERNAME = p.getProperty("jdbc_username");
		DB_PASSWORD = p.getProperty("jdbc_password");
	}
	
	public DataBaseUtil() {
	}
	
	/**
	 * 使用配置文件中的连接信息获取连接
	 */
	public Connection getConn() {
		try {
			Class.forName(DB_DRIVER);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (SQLException e) {
			System.err.println("Get connection failure！" + e);
		} catch (ClassNotFoundException e) {
			System.err.println("Load DB Driver failure!" + e);
		}
		return connection == null ? connection : null;
	}
	
	/////////////////////////////////////////////////////////////////////////////////////
	/**
	 * 使用非配置文件的连接信息
	 * 
	 * @param driver
	 *            数据路驱动
	 * @param url
	 *            数据库连接
	 * @param username
	 *            数据库用户名
	 * @param password
	 *            数据库密码
	 * @return Connection
	 */
	public Connection getConn(String driver, String url, String username, String password) {
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			System.err.println("Get connection failure！" + e);
			releaseConn();
		} catch (ClassNotFoundException e) {
			System.err.println("Load DB Driver failure!" + e);
		}
		return connection == null ? connection : null;
	}
	//////////////////////////////////////////////////////////////////////////////////////
	
	/**
	 * 插入
	 * 
	 * @param sql
	 *            prepareStatement格式的插入语句
	 * @param param
	 *            需要插入的单个数值
	 * @return 自增的情况下返回插入的主键
	 * @throws SQLException
	 */
	public Object insertReturnPK(String sql, Object param) throws SQLException {
		return insertReturnPK(sql, Arrays.asList(param));
	}
	
	/**
	 * 
	 * @param sql
	 *            prepareStatement格式的insert语句
	 * @param params
	 *            需要插入的多个参数,以list格式
	 * @return 返回插入语句的自增主键
	 * @throws SQLException
	 */
	public Object insertReturnPK(String sql, List params) throws SQLException {
		pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		pstmt.executeUpdate();
		resultSet = pstmt.getGeneratedKeys();
		Object pk = null;
		if (resultSet.next()) {
			pk = resultSet.getObject(1);
		}
		closePstmat();
		resultSet.close();
		return pk;
	}
	
	/**
	 * 执行某一条语句
	 * 
	 * @param sql
	 *            prepareStatement格式的sql语句
	 * @param param
	 *            填充的参数
	 * @return 返回是否执行成功
	 * @throws SQLException
	 */
	public boolean executePstamt(String sql, Object param) throws SQLException {
		return executePstamt(sql, Arrays.asList(param));
	}
	
	/**
	 * 执行某一条语句
	 * 
	 * @param sql
	 *            prepareStatement格式的sql语句
	 * @param param
	 *            填充的参数列表
	 * @return 返回是否执行成功
	 * @throws SQLException
	 */
	public boolean executePstamt(String sql, List params) throws SQLException {
		int result = -1;
		pstmt = connection.prepareStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		result = pstmt.executeUpdate();
		closePstmat();
		return result > 0 ? true : false;
	}
	
	/**
	 * 批量执行
	 * 
	 * @param sql
	 *            prepareStatement格式的sql语句
	 * @param lists
	 *            需要插入的数据列表,注:List<List> 格式,子list中为一条语句所填充的参数列表
	 * @return 返回是否执行成功
	 * @throws SQLException
	 */
	public boolean batchExecutePstamt(String sql, List<List> lists) throws SQLException {
		connection.setAutoCommit(false);
		boolean flag = false;
		int resultNum = 0;
		pstmt = connection.prepareStatement(sql);
		if (lists != null && !lists.isEmpty()) {
			for (List<Object> cList : lists) {
				if (cList == null || cList.isEmpty()) continue;
				
				for (int i = 0; i < cList.size(); i++) {
					pstmt.setObject(i + 1, cList.get(i));
				}
				pstmt.addBatch();
			}
			int[] resNum = pstmt.executeBatch();
			connection.commit();
			resultNum += resNum.length;
		}
		closePstmat();
		flag = resultNum > 0 ? true : false;
		return flag;
	}
	
	/**
	 * 查询一条,返回结果
	 * 
	 * @param sql
	 *            prepareStatement格式语句
	 * @param param
	 *           	不可为空
	 * @return 一个map集合
	 * @throws SQLException
	 */
	public Map selectOne(String sql, String param) throws SQLException {
		return selectOne(sql, Arrays.asList(param));
	}
	
	/**
	 * 查询一条,返回结果
	 * 
	 * @param sql
	 *            prepareStatement格式语句
	 * @param param
	 *            需要的参数列表,可为空
	 * @return 一个map集合
	 * @throws SQLException
	 */
	public Map selectOne(String sql, List params) throws SQLException {
		Map map = new LinkedHashMap();
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
		}
		closeResultSet();
		closePstmat();
		return map;
	}
	
	/**
	 * 查询多个结果的返回集
	 * 
	 * @param sql
	 *            prepareStatement格式语句
	 * @param params
	 *            需要填充的参数列表,可为空
	 * @return 一个list<map>的结果集
	 * @throws SQLException
	 */
	public List<Map> selectMore(String sql, List params) throws SQLException {
		List<Map> list = new ArrayList<Map>();
		int index = 1;
		pstmt = connection.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map map = new LinkedHashMap();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}
		closeResultSet();
		closePstmat();
		return list;
	}
	
	/**
	 * 查询列名
	 * 
	 * @param tanleN
	 *            表名称
	 * @return 列名称列表
	 * @throws SQLException
	 */
	public List<String> selectColumns(String tanleN) throws SQLException {
		DatabaseMetaData data;
		List<String> result = new ArrayList<>();
		data = connection.getMetaData();
		ResultSet columns = data.getColumns(null, null, tanleN, null);
		while (columns.next()) {
			result.add(columns.getString("COLUMN_NAME"));
		}
		columns.close();
		return result;
	}
	
	/**
	 * 获取插入语句
	 * 
	 * @param tableName
	 * @param fields
	 * @param isIGNORE
	 *            是否去重复
	 * @return
	 */
	public static String getInsertSql(String tableName, List<String> fields, boolean isIGNORE) {
		StringBuffer sb = new StringBuffer();
		if (isIGNORE) {
			sb.append("INSERT IGNORE INTO `").append(tableName).append("` ");
		} else {
			sb.append("INSERT INTO `").append(tableName).append("` ");
		}
		
		StringBuffer field = new StringBuffer("(");
		StringBuffer value = new StringBuffer("(");
		int sign = 0;
		for (String f : fields) {
			if (sign > 0) {
				field.append(",");
				value.append(",");
			}
			field.append("`").append(f).append("`");
			value.append("?");
			sign++;
		}
		field.append(")");
		value.append(")");
		sb.append(field).append(" values ").append(value).append(";");
		return sb.toString();
	}
	
	/**
	 * 获取建表语句
	 * 
	 * @param tableName
	 * @param fields
	 *            Map<String,String> 结构为[字段名,字段数据类型],如:[{name=varchar(64),lastname=varchar(64)}]
	 * @param engine
	 *            :InnoDB/MyISAM
	 * @return create sql
	 */
	public static String createTableSql(String tableName, Map<String, String> fields, String engine) {
		StringBuffer sb = new StringBuffer();
		sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");
		int sign = 0;
		for (Entry<String, String> entry : fields.entrySet()) {
			String field = entry.getKey();
			String type = entry.getValue();
			if (sign > 0) sb.append(",");
			sb.append("`").append(field).append("` ");
			sb.append(type).append(" null default null");
		}
		sb.append(")");
		sb.append(" COLLATE='utf8_general_ci' ").append(" ENGINE=").append(engine).append(";");
		return sb.toString();
	}
	
	/**
	 * 获取建表语句
	 * 
	 * @param tableName
	 * @param fields
	 *            一个list<string> 的字段列表
	 * @param engine
	 *            数据库引擎类型
	 * @param defaultFieldType
	 *            默认数据字段的类型,如:varchar(64)
	 * @return
	 */
	public static String createTableSql(String tableName, List<String> fields, String engine, String defaultFieldType) {
		StringBuffer sb = new StringBuffer();
		sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");
		
		int sign = 0;
		for (String field : fields) {
			if (sign > 0) sb.append(",");
			sb.append("`").append(field).append("` ");
			sb.append(defaultFieldType).append(" null default null");
		}
		
		sb.append(")");
		sb.append(" COLLATE='utf8_general_ci' ").append(" ENGINE=").append(engine).append(";");
		return sb.toString();
	}
	
	/**
	 * 验证表是否存在
	 * 
	 * @param tableName
	 *            表名称
	 * @return true/false
	 */
	public boolean validateTableExist(String tableName) {
		boolean flag = false;
		try {
			DatabaseMetaData meta = connection.getMetaData();
			String type[] = { "TABLE" };
			ResultSet rs = meta.getTables(null, null, tableName, type);
			flag = rs.next();
		} catch (SQLException e) {
			System.err.println("ERROR! validateTableExist failure! msg:[" + e + "]");
			e.printStackTrace();
		}
		return flag;
	}
	
	/*
	 * 释放链接
	 */
	public void releaseConn() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void closeResultSet() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void closePstmat() {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
